from Tkinter import *
from tkMessageBox   import askokcancel, showerror
from tkSimpleDialog import askstring
from TreeWidget import TreeItem, TreeNode, ScrolledCanvas

class Fancybutton(Button):
    def __init__(self, *a, **k):
        for ommit in  ('tooltip', 'icon'):
            if ommit in k:
                del k[ommit]
        Button.__init__(self, *a, **k)

class DbTreeItem(TreeItem):
    """
    TreeItem subclass -- browse the Db
    """

    def __init__(self, table, key=None, filter_table=None):
        """
        """
        self.table = table
        self.key = key
        self.filter_table = filter_table
        self.i_am_expandable = True

    def __repr__(self):
        """
        """
        if self.filter_table is not None:
            return '%s where %s Key:%d' % (self.table.name, self.filter_table.name, self.key)
        if self.key is not None:
            return '%s Key:%d' % (self.table.name, self.key)
        return self.table.name
            
    def GetText(self):
        """
        """
        if self.filter_table is not None:
            return self.table.name
        if self.key is None:
            return self.table.name
        return self.table.get_row_nice (self.key).replace('  ', ' ')
        
    def IsExpandable(self):
        """
        """
        return self.i_am_expandable

    def GetSubList(self):
        """
        """
        sublist = []
        if self.key is None:
            for key in self.table.get_rows_key()[1:]:
                key = int(key[0])
                sublist.append( DbTreeItem(self.table, key=key) )

        elif self.filter_table is None:
            for table in self.table.assoc:
                sublist.append( DbTreeItem(self.table.db[table], filter_table=self.table, key=self.key) )

        else:
            # build up a list of keys to compare using sql
            keycol = ['key']
            for col in self.table.cols:
                if col.foreign == self.filter_table.name:
                    keycol.append(col.name)
            sql = 'select %s from %s' % (','.join(keycol), self.table.name)

            # find all keys where the filter matches
            for row in self.table.sql(sql).fetchall():
                if self.key in row[1:]:
                   sublist.append( DbTreeItem(self.table, key=row[0]) )

        self.i_am_expandable = len(sublist) > 0
        return sublist

class DbTree(object):
    """
    Display parts of the database using a tree in a scrolled canvas.
    """
    
    def __init__(self, db, table):
        """
        """
        parent = Toplevel()
        parent.title(table.name)
        root=DbApp.neo.root
        root.update_idletasks()
        ww, hh = parent.winfo_width(), parent.winfo_height()
        w,h = root.winfo_screenwidth(), root.winfo_screenheight()
        parent.geometry('+%d+%d'% (w/2-ww/2, 32))
        parent.focus_set()
        sc = ScrolledCanvas(parent, bg="white", highlightthickness=0, takefocus=True)
        sc.frame.pack(expand=YES, fill=BOTH)
        TreeNode(sc.canvas, None, DbTreeItem(table)).expand()
    
class DbList(object):
    """
    """
    
    def __init__(self,db,table,frame=None):
        """
        """
        self.result=None
        if not frame:
            parent = Toplevel()
            parent.title(table.name)
        else:
            parent = frame
        self.db=db
        self.table=table
        f = Frame(parent)
        f.pack(side=BOTTOM,expand=YES,fill=X)
        Fancybutton(f, text='OK',command=self.handleOK).pack(side=LEFT,expand=YES,fill=X)
        Fancybutton(f, text='Cancel',command=self.handleCancel).pack(side=RIGHT,expand=YES,fill=X)
        f = Frame(parent)
        f.pack(side=TOP,expand=YES,fill=BOTH)
        s = Scrollbar(f)
        sb = Scrollbar(f,orient=HORIZONTAL)
        l = Listbox(f, bg='white',font='courier')
        s.config(command=l.yview,relief=SUNKEN)
        sb.config(command=l.xview,relief=SUNKEN)
        l.config(yscrollcommand=s.set,relief=SUNKEN)
        l.config(xscrollcommand=sb.set,relief=SUNKEN)
        s.pack(side=RIGHT,fill=BOTH)
        sb.pack(side=BOTTOM,fill=BOTH)
        l.pack(side=LEFT,expand=YES,fill=BOTH)
        maxlen=0
        for key in table.get_rows_key():
            key = int(key[0])
            if key == 0:
                continue
            line = table.get_row_nice(key)
            maxlen=max(maxlen,len(line))
            l.insert(END,line)
        l.config(width=maxlen)
        self.list=l
        self.key='None'
        self.parent=parent
        parent.update_idletasks()
        if not frame:
            root=DbApp.neo.root
            root.update_idletasks()
            parent.deiconify()
            ww, hh = parent.winfo_width(), parent.winfo_height()
            w,h = root.winfo_screenwidth(), root.winfo_screenheight()
            parent.geometry('+%d+%d'% (w/2-ww/2, 32))
        parent.focus_set()
        parent.grab_set()
        parent.wait_window()
        
    def handleOK(self):
        """
        """
        l=self.list
        selection=l.curselection()
        if not selection:return # not selected any yet 
        row=l.get(selection)
        self.result=row
        self.parent.destroy()
            
    def handleCancel(self):
        """
        """
        self.parent.destroy()
        
class DbEdit(object):
    """
    """
    
    def __init__(self, db, table, frame=None, filter=None, reactivate=None):
        """
        """
        self.db=db
        self.table=table
        self.keys=table.keys()
        self.index=0
        self.row={}
        self.filter=filter
        self.current_key=None
        self.reactivate_callback = reactivate
        if not frame:
            parent=Toplevel();
            parent.title(db.name)
        else:
            parent=Frame(frame)
            parent.pack()
        self.parent=parent
        Label(parent, text=table.name, relief=SUNKEN, bg='black', fg='white').pack(expand=YES,fill=X)
        for col in table.cols:
            head=col.name
            f = Frame(parent)
            l = Label(f, text=head, relief=RIDGE, width=30, bg='lightblue')
            v = StringVar()
            e = Entry (f,relief=SUNKEN,width=70,textvariable=v,state=DISABLED)
            if col.foreign:
                b = Fancybutton(f,relief=RAISED,width=5,state=DISABLED,text='List', tooltip='show available values', command=(lambda s=self,a=db.tables[col.foreign],v=v: s.listhandler(a,v)))
            elif col.type == 'varchar':
                b = Fancybutton(f,relief=RAISED,width=5,state=DISABLED, text='Edit', tooltip='edit in editor (not yet available)')
            else:
                b = Fancybutton(f,relief=RAISED,width=5,state=DISABLED, text=' ')
                
            self.row[head]=v,e,b
            b.pack(side=RIGHT)
            f.pack(side=TOP)
            l.pack(side=LEFT)
            e.pack(side=LEFT)
        f = Frame(parent)
        f.pack()
        width=5
        b=self.filter_status=Label(f,width=20,relief=SUNKEN)
        b.pack(side=LEFT)
        b=self.prev_button=Fancybutton(f, text='Prev', tooltip='select previous row', width=width, command=self.prev)
        b.pack(side=LEFT)
        b=self.next_button=Fancybutton(f, text='Next', tooltip='select next row', width=width, command=self.next)
        b.pack(side=LEFT)
        b=self.find_button=Fancybutton(f, text='Find', width=width, tooltip='set a filter (not yet available)', command=self.find, state=DISABLED)
        b.pack(side=LEFT)
        b=self.edit_button=Fancybutton(f, text='Edit', tooltip='edit current row data', width=width, command=self.edit)
        b.pack(side=LEFT)
        b=self.delete_button=Fancybutton(f, text='Delete', tooltip='admin only delete current row data', width=width, command=self.delete)
        b.pack(side=LEFT)
        b=self.new_button=Fancybutton(f, text='New', tooltip='create a new row', width=width, command=self.new)
        b.pack(side=LEFT)
        b=self.save_button=Fancybutton(f, text='Save', tooltip='save the new or modified data', width=width, command=self.save, state=DISABLED)
        b.pack(side=LEFT)
        b=self.cancel_button=Fancybutton(f, text='Cancel', tooltip='cancel new or modified data',width=width,command=self.cancel, state=DISABLED)
        b.pack(side=LEFT)
        b=self.close_button=Fancybutton(f, text='Close', tooltip='when edits are complete', width=width, command=self.close_cmd)
        b.pack(side=LEFT)
        b=self.status=Label(f,width=20,relief=SUNKEN)
        b.pack(side=LEFT)
        f = Frame(parent)
        f.pack()
        self.link_buttons = []
        for link in table.assoc:
            fb =Fancybutton(f,text=link,tooltip='open table', command=(lambda s=self,l=db.tables[link]: s.linkhandler(l)))
            fb.pack(side=LEFT)
            self.link_buttons.append(fb)
        if not frame:
            root = DbApp.neo.root
            root.update_idletasks()
            ww, hh = parent.winfo_width(), parent.winfo_height()
            w,h = root.winfo_screenwidth(), root.winfo_screenheight()
            parent.geometry('+%d+%d'% (w/2-ww/2, 32))
        self.refresh()
        self.show()
        self.prev()

    def close_cmd(self):
        """
        """
        if self.reactivate_callback:
            self.reactivate_callback()
        self.parent.destroy()
        
    def deactivate(self):
        """
        """
        for lb in self.link_buttons:
            lb.config(state=DISABLED)
        self.next_button.config  (state=DISABLED)
        self.prev_button.config  (state=DISABLED)
        self.edit_button.config  (state=DISABLED)
        self.new_button.config   (state=DISABLED)

    def reactivate(self):
        """
        """
        for lb in self.link_buttons:
            lb.config(state=NORMAL)
        self.next_button.config  (state=NORMAL)
        self.prev_button.config  (state=NORMAL)
        self.edit_button.config  (state=NORMAL)
        self.new_button.config   (state=NORMAL)
        
    def linkhandler(self,l):
        """
        """
        self.deactivate()
        DbEdit(self.db, l, frame=self.parent, filter=(self.table.name, self.current_key), reactivate=self.reactivate)
        
    def listhandler(self,c,s):
        """
        """
        x=DbList(self.db,c)
        if x.result:
            s.set(x.result)
        
    def next(self):
        """
        """
        if len(self.keys)==0:
            return
        self.index += 1
        if len(self.keys)<=self.index:
            self.index -= 1
            return
        self.show()

    def prev(self):
        """
        """
        if len(self.keys)==0:
            return
        self.index -= 1
        if self.index<0:
            self.index=0
            return
        self.show()

    def only_new_button_on(self):
        """
        """
        for lb in self.link_buttons:
            lb.config(state=DISABLED)
        self.next_button.config  (state=DISABLED)
        self.prev_button.config  (state=DISABLED)
        self.find_button.config  (state=DISABLED)
        self.edit_button.config  (state=DISABLED)
        self.new_button.config   (state=NORMAL)
        self.delete_button.config(state=DISABLED)
        self.save_button.config  (state=DISABLED)
        self.cancel_button.config(state=DISABLED)
        
    def save_buttons_on(self):
        """
        """
        for lb in self.link_buttons:
            lb.config(state=DISABLED)
        self.next_button.config  (state=DISABLED)
        self.prev_button.config  (state=DISABLED)
        self.find_button.config  (state=DISABLED)
        self.edit_button.config  (state=DISABLED)
        self.new_button.config   (state=DISABLED)
        self.delete_button.config(state=DISABLED)
        self.save_button.config  (state=NORMAL)
        self.cancel_button.config(state=NORMAL)

    def save_buttons_off(self):
        """
        """
        for lb in self.link_buttons:
            lb.config(state=NORMAL)
        self.next_button.config  (state=NORMAL)
        self.prev_button.config  (state=NORMAL)
        self.find_button.config  (state=DISABLED)
        self.edit_button.config  (state=NORMAL)
        self.new_button.config   (state=NORMAL)
        self.delete_button.config(state=DISABLED)
        self.save_button.config  (state=DISABLED)
        self.cancel_button.config(state=DISABLED) 

    def new(self):
        """
        """
        self.save_buttons_on()
        self.current_key=None
        for col in self.table.cols:
            head=col.name
            var, widget, button = self.row[head]
            var.set('')
            if self.filter:
                ftable, fkey = self.filter
                if col.foreign == ftable:
                    foreign_table=self.db[col.foreign]
                    var.set(foreign_table.get_row_nice(int(fkey)))
            if col.foreign:
                button.config(state=NORMAL)
            else:
                widget.config(state=NORMAL,bg='lightyellow')
        self.status.config(text='New')

    def save(self):
        """
        after for new or edit
        """
        self.status.config(text='Save')
        if not self.current_key: #new
            self.current_key = self.table.new()
        else: #edit
            self.table.access(self.current_key)
        for col in self.table.cols:
            head=col.name
            var,widget,button = self.row[head]
            val = var.get()
            if val:
                try:
                    if col.foreign:
                        val=int(val.replace('Key:','').split('|',1)[0])
                    else:
                        val=col.cnv(val)
                    self.table[head]=val
                except:
                    print 'exception',col,val
        self.cancel(self.current_key)
        
    def cancel(self, key=None):
        """
        """
        for col in self.table.cols:
            head=col.name
            var,widget,button=self.row[head]
            widget.config(state=DISABLED,bg='white')
            if col.foreign:
                button.config(state=DISABLED)
        self.save_buttons_off()
        self.refresh(key)
        
    def _apply_filter(self):
        """
        Set filter status and keys.
        """
        find=self.filter
        if not find:
            self.filter_status.config(text='')
            self.keys=self.table.keys() #refresh keys
            return

        filter_table, filter_key = find

        # build up a list of keys to compare using sql
        keycol = ['key']
        for col in self.table.cols:
            if col.foreign == filter_table:
                keycol.append(col.name)
        sql = 'select %s from %s' % (','.join(keycol), self.table.name)

        # find all keys where the filter matches
        self.filter_status.config(text='%s Key:%s' % (filter_table, filter_key))
        self.keys=[]
        for row in self.table.sql(sql).fetchall():
            if filter_key in row[1:]:
               self.keys.append(row[0])
                
    def refresh(self, key=None):
        """
        """
        self._apply_filter()
        if key is None or key not in self.keys:
            self.index=0 #reset index
        else:
            self.index = self.keys.index(key)
        self.show()
        self.parent.focus_set()
        
    def show(self):
        """
        """
        if len(self.keys)==0:
            for col in self.table.cols:
                head=col.name
                var,widget,button=self.row[head]
                var.set('')
            self.status.config(text='')
            self.only_new_button_on()
            return
        key=self.keys[self.index]
        self.current_key=key
        self.status.config(text='Key:'+str(key))
        for col in self.table.cols:
            head=col.name
            var,widget,button=self.row[head]
            val=col[key]
            if col.foreign:
                foreign_table=self.db[col.foreign]
                val = foreign_table.get_row_nice(int(val)) #lookup the value
            var.set(val)

    def find(self):
        """
        """
        if len(self.keys)==0:
            return
        self.filter = askstring('Find','What are you looking for?').lower()
        self.refresh()
        
    def edit(self):
        """
        """
        if len(self.keys)==0:
            return
        self.status.config(text='Edit')
        self.save_buttons_on()
        for col in self.table.cols:
            head=col.name
            var,widget,button=self.row[head]
            val=col[self.current_key]
            if col.foreign:
                foreign_table=self.db[col.foreign]
                val =  foreign_table.get_row_nice(int(val)) #lookup the value
            var.set(val)
            if col.foreign:
                button.config(state=NORMAL)
            else:
                widget.config(state=NORMAL,bg='lightyellow')
        
    def delete(self):
        """
        """
        if len(self.keys)==0:
            return
        if not DbApp.neo.is_admin():
            showerror('', 'Must be admin to delete')
            return
        if askokcancel('', 'Delete \n%s\nAre you sure?' % self.table.get_row_nice(self.current_key, compact=True)):
            self.table.access(key=self.current_key)
            self.table.oops()
            self.refresh()
        
class TkDbDisplay(object):
    """
    """
    
    def __init__(self,f1,f2,f3,db,table):
        """
        """
        self.db=db
        self.table=table
        Fancybutton(f1,text=table.name, tooltip='edit %s' % table.name, command=self.form_cmd, bg='blue').pack(side=LEFT)
        Fancybutton(f2,text=table.name, tooltip='view list for %s' % table.name, command=self.list_cmd, bg='green').pack(side=LEFT)
        Fancybutton(f3,text=table.name, tooltip='view tree for %s' % table.name, command=self.tree_cmd, bg='red').pack(side=LEFT)
        
    def form_cmd(self):
        """
        """
        DbEdit(self.db,self.table)
        
    def list_cmd(self):
        """
        """
        DbList(self.db,self.table)

    def tree_cmd(self):
        """
        """
        DbTree(self.db,self.table)

class DbApp(object):
    """
    """
    neo = None

    def __init__(self,db):
        """
        """
        DbApp.neo = self
        self.root=root=Tk()
        root.focus_set()
        self.db=db
        root.title(db.name)
        root.protocol('WM_DELETE_WINDOW',self.close_window)
        f1=Frame(root)
        f1.pack(side=TOP)
        f2=Frame(root)
        f2.pack(side=TOP)
        f3=Frame(root)
        f3.pack(side=TOP)
        Label(f1,width=8,text='Edit').pack(side=LEFT)
        Label(f2,width=8,text='View').pack(side=LEFT)
        Label(f3,width=8,text='Tree').pack(side=LEFT)

        for table in db.tables.values():
            TkDbDisplay(f1,f2,f3,db,table)

        f=Frame()
        f.pack(side=TOP)
        Checkbutton(f,text='Admin',command=self.toggle_admin).pack(side=LEFT)
        createtb=self.createtb=Fancybutton(f, text='New (empty) db', command=db.create, state=DISABLED, tooltip='create an empty db')
        createtb.pack(side=LEFT)
        populateb=self.populateb=Fancybutton(f, text='Populate', tooltip='populate with default data', command=db.populate, state=DISABLED)
        populateb.pack(side=LEFT)
        Fancybutton(f, text='Connect', command=db.connect, tooltip='connect to db').pack(side=LEFT)
        Fancybutton(f, text='Verify', tooltip='verify data in all tables', command=lambda f=db.all_tables,w='verify':f(w)).pack(side=LEFT)
        Fancybutton(f, text='Repair', tooltip='repair errors in all tables (not yet available)', command=lambda f=db.all_tables,w='repair':f(w)).pack(side=LEFT)
        Fancybutton(f, text='Commit', tooltip='commit all changes to database', command=db.commit).pack(side=LEFT)
        Fancybutton(f, text='Dump', tooltip='list database contents to screen', command=lambda f=db.all_tables,w='dump':f(w)).pack(side=LEFT)
        Fancybutton(f, text='Write Csv', tooltip='write csv files for all tables to database folder for backup', command=lambda f=db.all_tables,w='write_csv':f(w)).pack(side=LEFT)
        Fancybutton(f, text='Introspect', tooltip='list database structure to screen', command=db.introspect).pack(side=LEFT)
        Fancybutton(f, text='Done', command=self.close_window, tooltip='exit with commit').pack(side=LEFT)
        Fancybutton(f, text='Abort', command=self.root.quit, tooltip='exit (no commit)').pack(side=LEFT)
        Checkbutton(f,text='Debug',command=self.toggle_debug).pack(side=LEFT)

        root.update_idletasks()
        ww, hh = root.winfo_width(), root.winfo_height()
        w,h = root.winfo_screenwidth(), root.winfo_screenheight()
        root.geometry('%dx%d+%d+%d'% (ww, hh, w/2-ww/2, h-200))
        mainloop()

    def close_window(self):
        """
        """
        if self.db.connection is None:
            self.root.quit()
            return
        if askokcancel('Quit','Quit Are you sure?'):
            if askokcancel('Commit','Commit Are you sure?'):
                self.db.commit()
            self.root.quit()

    def is_admin(self):
        """
        """
        return self.createtb.cget('state') == NORMAL
    
    def toggle_admin(self):
        """
        """
        createtb=self.createtb
        populateb=self.populateb
        if createtb.cget('state') == NORMAL:
            createtb.config(state=DISABLED)
            populateb.config(state=DISABLED)
        else:
            createtb.config(state=NORMAL)
            populateb.config(state=NORMAL)

    def toggle_debug(self):
        """
        """
        Db.debug = not Db.debug

